package com.wzw.tcjh;

import java.util.*;
import java.sql.*;

import javax.servlet.ServletContext;

/**
 * SQLProcessor - provides utitlity functions to ease database access
 * 
 * @author jeff
 *  
 */
public class SQLProcessor {

    // / The database resources to be used
    private Connection _connection = null;

    // / The database resources to be used
    private PreparedStatement _ps = null;

    // / The database resources to be used
    private Statement _stmt = null;

    // / The database resources to be used
    private ResultSet _rs = null;

    // / The SQL String used. Use for debugging only
    private String _sql;

    // / true in case of manual transactions
    private boolean _manualTX;

    // / true in case the connection shall be closed.
    private boolean _bDeleteConnection = false;

    private ServletContext _context = null;

    private int resultCount = 0;

    /**
     * Construct an object with an connection given. The connection will not be
     * closed by this SQLProcessor, but may be by some other.
     * 
     * @param connection
     *            The connection to be used
     */
    public SQLProcessor(Connection connection, ServletContext context) {
        this._connection = connection;
        // Do not commit while closing
        if (_connection != null) {
            _manualTX = false;
        }
        this._context = context;
    }

    public SQLProcessor(ServletContext context) throws Exception{
    	
    	if(false)
    	{
	    	try
	    	{
	    		throw new Exception("Andy 在数据更新，请耐心等待，恭喜发财:)");
	    	}catch(Exception e)
	    	{
	    		throw new Exception("Andy 知道你看到天书般的错误信息代码了，" +
	    				"因为 Andy 在维护数据和服务器，" +
	    				"为了提高更新效率，" +
	    				"Andy只能先暂停网页浏览数据库了，请耐心等待，恭喜发财:)" +
	    				"Andy 的工作就是每天和这些天书代码打交道！");
	    	}
    	}
	    else
	    {
	        String dbName = context.getInitParameter("dbName");
	        this._connection = getConnection(dbName);
	        this._context = context;
	        this._manualTX = true;
	    }
    }
    
    public SQLProcessor() {        
        this._connection = getConnection("");        
        this._manualTX = true;
    }
    
    public SQLProcessor(boolean needds) {        
        this._connection = getConnection(needds);        
        this._manualTX = true;
    }
    
    

    public Connection getConnection(String dbName) {
        if (_connection != null) return _connection;

        _manualTX = true;

        //_connection = new DbClass(dbName,true).getConnection(true);
        _connection = new DbClass(dbName,false).getConnection(false);

        // always try to set auto commit to false, but if we can't then later on
        // we won't commit
        try {
            _connection.setAutoCommit(false);
        } catch (SQLException sqle) {
            _manualTX = false;
        }

        _bDeleteConnection = true;
        return _connection;
    }
    public Connection getConnection(boolean needds) {
        if (_connection != null) return _connection;

        _manualTX = true;

        _connection = new DbClass("",needds).getConnection(needds);

        // always try to set auto commit to false, but if we can't then later on
        // we won't commit
        try {
            _connection.setAutoCommit(false);
        } catch (SQLException sqle) {
            _manualTX = false;
        }

        _bDeleteConnection = true;
        return _connection;
    }
    public void setAutoCommit() {
        if (_connection == null) return ;

        _manualTX = false;

        // always try to set auto commit to false, but if we can't then later on
        // we won't commit
        try {
            _connection.setAutoCommit(true);
        } catch (SQLException sqle) {
            _manualTX = true;
        }
    }

    public Connection getConnection() {
        return _connection;
    }

    public int getResultCount() {
        return resultCount;
    }

    /**
     * Prepare a statement. In case no connection has been given, allocate a new
     * one.
     * 
     * @param sql
     *            The SQL statement to be executed
     * 
     * @throws SQLException
     */
    public void prepareStatement(String sql) throws SQLException {
        this.prepareStatement(sql, false, 0, 0);
    }

    /**
     * Prepare a statement. In case no connection has been given, allocate a new
     * one.
     * 
     * @param sql
     *            The SQL statement to be executed
     * 
     * @throws SQLException
     */
    public void prepareStatement(String sql, boolean specifyTypeAndConcur,
            int resultSetType, int resultSetConcurrency) throws SQLException {

        if (_connection == null) {
            //getConnection();
        }

        try {
            _sql = sql;
            if (specifyTypeAndConcur) {
                _ps = _connection.prepareStatement(sql, resultSetType,
                        resultSetConcurrency);
            } else {
                _ps = _connection.prepareStatement(sql);
            }
        } catch (SQLException sqle) {
            throw sqle;
        }
    }


    public List executeQuery(String sql, int start, int number)
    throws SQLException {
		prepareStatement(sql, true, ResultSet.TYPE_SCROLL_INSENSITIVE,
		        ResultSet.CONCUR_READ_ONLY);
		return executeQuery(start, number);
		}

    /**
     * Execute a query baed ont SQL string given
     * 
     * @param sql
     *            The SQL string to be executed
     * @return The result set of the query
     * @throws SQLException
     */
    public List executeQuery(String sql) throws SQLException {
        prepareStatement(sql);
        return executeQuery();
    }


    /**
     * added by Stephen Ting 2005-10-11
     * @param sqlName
     * @param batchParam
     * @return
     * @throws SQLException
     */
    public void addBatch(String sql) throws SQLException {
    	if(_stmt==null)
    	{
    		_stmt = _connection.createStatement();
    	}
		_stmt.addBatch(sql);
    }
    
    public int[] executeBatch() throws SQLException {    	
       return _stmt.executeBatch();       
    }
    

    /**
     * Execute updates
     * 
     * @return The number of rows updated
     * @throws SQLException
     */
    private int executeUpdate() throws SQLException {
        return _ps.executeUpdate();
    }

    /**
     * Execute update based on the SQL statement given
     * 
     * @param sql
     *            SQL statement to be executed
     * @throws SQLException
     */
    public int executeUpdate(String sql) throws SQLException {
        Statement stmt = null;

        try {
            stmt = _connection.createStatement();
            //System.out.println(stmt);
            return stmt.executeUpdate(sql);
        } catch (SQLException sqle) {
            throw sqle;
        } finally {
            if (stmt != null) {
                stmt.close();
            }
        }
    }
    
    public int executeUpdateWithOwnstmt(String sql) throws SQLException {
    	if(_stmt==null)
    	{
    		_stmt = _connection.createStatement();
    	}
		return _stmt.executeUpdate(sql);
    }

    private List executeQuery() throws SQLException {
        List resultList = new ArrayList();
        _rs = _ps.executeQuery();
        while (_rs.next()) {
            Map resultMap = new HashMap();
            int columnCount = _rs.getMetaData().getColumnCount();
            for (int i = 1; i <= columnCount; i++) {
                String label = _rs.getMetaData().getColumnLabel(i);
                //System.out.println(label);
                //System.out.println(_rs.getObject(i));
                resultMap.put(label, _rs.getObject(i)==null?"":_rs.getObject(i));
                if (_rs.getMetaData().getColumnTypeName(i).indexOf("date") != -1
                        && _rs.getDate(i)!=null &&_rs.getDate(i).getTime() == -2209017600000L)
                    resultMap.put(label, "");
            }
            resultList.add(resultMap);
        }
        resultCount = resultList.size();
        return resultList;
    }

    private List executeQuery(int start, int number) throws SQLException {
        List resultList = new ArrayList();
        _rs = _ps.executeQuery();
        //System.out.println("_rs="+_rs.getString("stockcode"));
        if (_rs.last()) resultCount = _rs.getRow();
        //System.out.println("resultCount="+resultCount);
        if (resultCount == 0) return resultList;
        if (!_rs.absolute(start)) 
        { //throw new SQLException(
                //"Could not move to the start position of "
                        //+ start
                       // + ", there are probably not that many results for this find."); 
        	return resultList;
        }
        int numRetreived = 1;
        do {
            Map resultMap = new HashMap();
            int columnCount = _rs.getMetaData().getColumnCount();
            for (int i = 1; i <= columnCount; i++) {
                String label = _rs.getMetaData().getColumnLabel(i);
                //System.out.println(label);
                resultMap.put(label, (_rs.getObject(i))==null?"":_rs.getObject(i));
                if (_rs.getMetaData().getColumnTypeName(i).indexOf("date") != -1
                		&& _rs.getDate(i)!=null && _rs.getDate(i).getTime() == -2209017600000L)
                    resultMap.put(label, "");
            }
            resultList.add(resultMap);
            numRetreived++;
        } while (number >= numRetreived && _rs.next());

        return resultList;
    }

    /**
     * Commit if required and remove all allocated resources
     * 
     * @throws SQLException
     */
    public void close() throws SQLException {
        if (_manualTX) {
            commit();
        }

        _sql = null;

        if (_rs != null) {
            try {
                _rs.close();
            } catch (SQLException sqle) {
                System.err.println(sqle.getMessage());
            }

            _rs = null;
        }

        if (_ps != null) {
            try {
                _ps.close();
            } catch (SQLException sqle) {
                System.err.println(sqle.getMessage());
            }

            _ps = null;
        }

        if (_stmt != null) {
            try {
                _stmt.close();
            } catch (SQLException sqle) {
                System.err.println(sqle.getMessage());
            }

            _stmt = null;
        }

        if ((_connection != null) && (_bDeleteConnection == true)) {
            try {
                _connection.close();
            } catch (SQLException sqle) {
                System.err.println(sqle.getMessage());
            }

            _connection = null;
        }
    }

    /**
     * Commit all modifications
     * 
     * @throws SQLException
     */
    public void commit() throws SQLException {
        if (_connection == null) { return; }

        if (_manualTX) {
            try {
                _connection.commit();
            } catch (SQLException sqle) {
                rollback();
                throw sqle;
            }
        }
    }

    /**
     * Rollback all modifications
     */
    public void rollback() throws SQLException {
        if (_connection == null) { return; }

        try {
            if (_manualTX) {
                _connection.rollback();
            }
        } catch (SQLException sqle2) {
            System.err
                    .println("[SQLProcessor.rollback]: SQL Exception while rolling back insert. Error was:"
                            + sqle2);
        
		}finally{
			_sql = null;

	        if (_rs != null) {
	            try {
	                _rs.close();
	            } catch (SQLException sqle) {
	                System.err.println(sqle.getMessage());
	            }

	            _rs = null;
	        }

	        if (_ps != null) {
	            try {
	                _ps.close();
	            } catch (SQLException sqle) {
	                System.err.println(sqle.getMessage());
	            }

	            _ps = null;
	        }

	        if (_stmt != null) {
	            try {
	                _stmt.close();
	            } catch (SQLException sqle) {
	                System.err.println(sqle.getMessage());
	            }

	            _stmt = null;
	        }

	        if ((_connection != null) && (_bDeleteConnection == true)) {
	            try {
	                _connection.close();
	            } catch (SQLException sqle) {
	                System.err.println(sqle.getMessage());
	            }

	            _connection = null;
	        }
		}
        
    }
    
    public static void staticClose(SQLProcessor sp){
    	try{
			if(sp!=null)
				sp.close();
		}catch(SQLException se){
			se.printStackTrace();
		}
    }

}